﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using XuanFe.Database.Model;

namespace XuanFe.Database.DAL
{
    public class StaffDAL
    {
        private const string SQL_SELECT_WS_STAFF = "select STF_STAFF_ID as STAFF_ID,STF_STAFF_NAME as STAFF_NAME,STF_PASSWORD as PASSWORD,STF_ROLE_ID as ROLE_ID,DATE_FORMAT(STF_JOIN_DATE,'%Y-%m-%d') as JOIN_DATE,STF_DIMISSION_FLG as DIMISSION_FLG,ROL_ROLE_NAME as ROLE_NAME,STF_RCRE_STAFF_ID as RCRE_STAFF_ID,STF_RCRE_TIME as RCRE_TIME,STF_LCHG_STAFF_ID as LCHG_STAFF_ID,STF_LCHG_TIME as LCHG_TIME,ROL_ROLE_LEVEL as ROLE_LEVEL from WS_STAFF,WS_ROLE where ROL_ROLE_ID=STF_ROLE_ID";
        private const string SQL_INSERT_WS_STAFF = "insert into WS_STAFF(STF_STAFF_ID,STF_STAFF_NAME,STF_PASSWORD,STF_ROLE_ID,STF_JOIN_DATE,STF_DIMISSION_FLG,STF_RCRE_STAFF_ID,STF_RCRE_TIME) values(@StaffId,@StaffName,@Password,@RoleId,@JoinDate,@DimissionFlag,@LastStaffId,@LastTime)";
        private const string SQL_UPDATE_WS_STAFF = "update WS_STAFF set STF_STAFF_NAME=@StaffName,STF_PASSWORD=@Password,STF_ROLE_ID=@RoleId,STF_JOIN_DATE=@JoinDate,STF_DIMISSION_FLG=@DimissionFlag,STF_LCHG_STAFF_ID=@LastStaffId,STF_LCHG_TIME=@LastTime where 1=1";
        private const string SQL_UPDATE_WS_STAFF_WITHOUT_PASSWORD = "update WS_STAFF set STF_STAFF_NAME=@StaffName,STF_ROLE_ID=@RoleId,STF_JOIN_DATE=@JoinDate,STF_DIMISSION_FLG=@DimissionFlag,STF_LCHG_STAFF_ID=@LastStaffId,STF_LCHG_TIME=@LastTime where 1=1";
        private const string SQL_DELETE_WS_STAFF = "delete from WS_STAFF where 1=1";
        private const string SQL_COUNT_WS_STAFF = "select count(STF_STAFF_ID) from WS_STAFF where 1=1";
        private const string SQL_UPDATE_PASSWORD = "update WS_STAFF set STF_PASSWORD=@Password where 1=1";

        private const string SQL_WHERE_STAFF_ID = " and STF_STAFF_ID=@StaffId ";
        private const string SQL_WHERE_STAFF_ID_NAME = " and STF_STAFF_ID<>@StaffId and STF_STAFF_NAME=@StaffName ";
        private const string SQL_WHERE_ROLE_LEVEL_NOT_LESS = " and ROL_ROLE_LEVEL>=@RoleLevel ";
        private const string SQL_WHERE_ROLE_ID = " and STF_ROLE_ID=@RoleId ";
        private const string SQL_WHERE_FOLLOW_FLG = " and ROL_FOLLOW_FLG=@FollowFlg ";

        private const string PARM_STAFF_ID = "@StaffId";
        private const string PARM_STAFF_NAME = "@StaffName";
        private const string PARM_PASSWORD = "@Password";
        private const string PARM_ROLE_ID = "@RoleId";
        private const string PARM_JOIN_DATE = "@JoinDate";
        private const string PARM_DIMISSION_FLAG = "@DimissionFlag";
        private const string PARM_LAST_STAFF_ID = "@LastStaffId";
        private const string PARM_LAST_TIME = "@LastTime";
        private const string PARM_ROLE_LEVEL = "@RoleLevel";
        private const string PARM_FOLLOW_FLG = "@FollowFlg";

        Nullable<DateTime> NullDateTime = null;

        private static StaffDAL instance = new StaffDAL();
        /// <summary>
        /// 初始化实例
        /// </summary>
        /// <returns></returns>
        public static StaffDAL Instance
        {
            get
            {
                return instance;
            }
        }
        public DataSet GetDataSet(int level)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_SELECT_WS_STAFF);
            sbSQL.Append(SQL_WHERE_ROLE_LEVEL_NOT_LESS);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_ROLE_LEVEL, MySqlDbType.Int32) { Direction = ParameterDirection.Input, Value = level });

            return MySQLHelper.Instance.ExecuteDataSet(MySQLHelper.Instance.ConnectionString, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        public StaffModel Get(string id)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_SELECT_WS_STAFF);
            sbSQL.Append(SQL_WHERE_STAFF_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = id });

            using (DataSet ds = MySQLHelper.Instance.ExecuteDataSet(MySQLHelper.Instance.ConnectionString, CommandType.Text, sbSQL.ToString(), parms.ToArray()))
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    DataRow t = ds.Tables[0].Rows[0];
                    return new StaffModel()
                    {
                        Id = Convert.ToString(t.ItemArray[0]),
                        Name = Convert.ToString(t.ItemArray[1]),
                        Password = Convert.ToString(t.ItemArray[2]),
                        RoleId = Convert.ToString(t.ItemArray[3]),
                        JoinDate = Convert.ToDateTime(t.ItemArray[4]),
                        Dimission = string.Equals(Convert.ToString(t.ItemArray[5]), "Y"),
                        CreatedStaffId = Convert.ToString(t.ItemArray[7]),
                        CreatedDate = Convert.ToDateTime(t.ItemArray[8]),
                        LastModifiedStaffId = t.IsNull(9) ? string.Empty : Convert.ToString(t.ItemArray[9]),
                        LastModifiedDate = t.IsNull(10) ? NullDateTime : Convert.ToDateTime(t.ItemArray[10])
                    };
                }
                return null;
            }
        }
        public List<StaffModel> Gets()
        {
            using (DataSet ds = MySQLHelper.Instance.ExecuteDataSet(MySQLHelper.Instance.ConnectionString, CommandType.Text, SQL_SELECT_WS_STAFF, null))
            {
                DataTable dt = ds.Tables[0];
                List<StaffModel> list = dt.AsEnumerable().Select(t => new StaffModel
                {
                    Id = Convert.ToString(t.ItemArray[0]),
                    Name = Convert.ToString(t.ItemArray[1]),
                    Password = Convert.ToString(t.ItemArray[2]),
                    RoleId = Convert.ToString(t.ItemArray[3]),
                    JoinDate = Convert.ToDateTime(t.ItemArray[4]),
                    Dimission = string.Equals(Convert.ToString(t.ItemArray[5]), "Y"),
                    CreatedStaffId = Convert.ToString(t.ItemArray[7]),
                    CreatedDate = Convert.ToDateTime(t.ItemArray[8]),
                    LastModifiedStaffId = t.IsNull(9) ? string.Empty : Convert.ToString(t.ItemArray[9]),
                    LastModifiedDate = t.IsNull(10) ? NullDateTime : Convert.ToDateTime(t.ItemArray[10])
                }).ToList<StaffModel>();

                return list;
            }
        }
        public List<StaffModel> Gets(bool follow)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_SELECT_WS_STAFF);
            sbSQL.Append(SQL_WHERE_FOLLOW_FLG);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_FOLLOW_FLG, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = follow ? "Y" : "N" });

            using (DataSet ds = MySQLHelper.Instance.ExecuteDataSet(MySQLHelper.Instance.ConnectionString, CommandType.Text, sbSQL.ToString(), parms.ToArray()))
            {
                DataTable dt = ds.Tables[0];
                List<StaffModel> list = dt.AsEnumerable().Select(t => new StaffModel
                {
                    Id = Convert.ToString(t.ItemArray[0]),
                    Name = Convert.ToString(t.ItemArray[1]),
                    Password = Convert.ToString(t.ItemArray[2]),
                    RoleId = Convert.ToString(t.ItemArray[3]),
                    JoinDate = Convert.ToDateTime(t.ItemArray[4]),
                    Dimission = string.Equals(Convert.ToString(t.ItemArray[5]), "Y"),
                    CreatedStaffId = Convert.ToString(t.ItemArray[7]),
                    CreatedDate = Convert.ToDateTime(t.ItemArray[8]),
                    LastModifiedStaffId = t.IsNull(9) ? string.Empty : Convert.ToString(t.ItemArray[9]),
                    LastModifiedDate = t.IsNull(10) ? NullDateTime : Convert.ToDateTime(t.ItemArray[10])
                }).ToList<StaffModel>();

                return list;
            }
        }
        public int Insert(DbTransaction trans, StaffModel model)
        {
            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Id });
            parms.Add(new MySqlParameter(PARM_STAFF_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Name });
            parms.Add(new MySqlParameter(PARM_PASSWORD, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Password });
            parms.Add(new MySqlParameter(PARM_ROLE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.RoleId });
            parms.Add(new MySqlParameter(PARM_JOIN_DATE, MySqlDbType.Date) { Direction = ParameterDirection.Input, Value = model.JoinDate });
            parms.Add(new MySqlParameter(PARM_DIMISSION_FLAG, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Dimission ? "Y" : "N" });
            parms.Add(new MySqlParameter(PARM_LAST_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.LastModifiedStaffId });
            parms.Add(new MySqlParameter(PARM_LAST_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.LastModifiedDate });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, SQL_INSERT_WS_STAFF, parms.ToArray());
        }
        public int Update(DbTransaction trans, StaffModel model)
        {
            StringBuilder sbSQL = new StringBuilder();
            if (!string.IsNullOrEmpty(model.Password))
            {
                sbSQL.Append(SQL_UPDATE_WS_STAFF);
            }
            else
            {
                sbSQL.Append(SQL_UPDATE_WS_STAFF_WITHOUT_PASSWORD);
            }
            sbSQL.Append(SQL_WHERE_STAFF_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Id });
            parms.Add(new MySqlParameter(PARM_STAFF_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Name });
            if (!string.IsNullOrEmpty(model.Password))
            {
                parms.Add(new MySqlParameter(PARM_PASSWORD, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Password });
            }
            parms.Add(new MySqlParameter(PARM_ROLE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.RoleId });
            parms.Add(new MySqlParameter(PARM_JOIN_DATE, MySqlDbType.Date) { Direction = ParameterDirection.Input, Value = model.JoinDate });
            parms.Add(new MySqlParameter(PARM_DIMISSION_FLAG, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Dimission ? "Y" : "N" });
            parms.Add(new MySqlParameter(PARM_LAST_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.LastModifiedStaffId });
            parms.Add(new MySqlParameter(PARM_LAST_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.LastModifiedDate });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        public int UpdatePassword(DbTransaction trans, StaffModel model)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_UPDATE_PASSWORD);
            sbSQL.Append(SQL_WHERE_STAFF_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Id });
            parms.Add(new MySqlParameter(PARM_PASSWORD, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Password });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        public int Delete(DbTransaction trans, string id)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_DELETE_WS_STAFF);
            sbSQL.Append(SQL_WHERE_STAFF_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = id });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        public bool Exist(string id)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_COUNT_WS_STAFF);
            sbSQL.Append(SQL_WHERE_STAFF_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = id });

            return MySQLHelper.Instance.RecordIsExist(MySQLHelper.Instance.ConnectionString, sbSQL.ToString(), parms.ToArray());
        }
        public bool Exist(string id, string name)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_COUNT_WS_STAFF);
            sbSQL.Append(SQL_WHERE_STAFF_ID_NAME);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = id });
            parms.Add(new MySqlParameter(PARM_STAFF_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = name });

            return MySQLHelper.Instance.RecordIsExist(MySQLHelper.Instance.ConnectionString, sbSQL.ToString(), parms.ToArray());
        }
        public bool RoleUsing(string roleId)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_COUNT_WS_STAFF);
            sbSQL.Append(SQL_WHERE_ROLE_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_ROLE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = roleId });

            return MySQLHelper.Instance.RecordIsExist(MySQLHelper.Instance.ConnectionString, sbSQL.ToString(), parms.ToArray());
        }
    }
}
